package utils.fileConvert;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.lkx.util.*;
import com.lkx.util.ExcelUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import utils.object.UtilValidate;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author Chen Yuan
 * @create 2022-01-04  10:02
 */
public class ExcelUtilBase {

	public static <T> List<T> commonReadExcel(ExcelParam excelParam) throws Exception {
		Set<String> keySet = null;
		Class<T> clazz = excelParam.getClazz();
		boolean isJsonObj = Objects.equals(clazz, JSONObject.class);
		//新加入了注解，如果map为空，则自动从class中的注解自动查找
		Map map = excelParam.getMap();
		if (map == null || map.size() == 0) {
			if (isJsonObj) {
				map = new LinkedHashMap(20);
			} else {
				excelParam.setMap(getMap(clazz));
			}
		}

		if (isJsonObj) {
			keySet = new HashSet<>(20);
		} else {
			keySet = map.keySet();
		}

		List<T> list = new ArrayList();
		String fileType = "";
		InputStream is = null;
		Workbook wb = null;
		if (excelParam.getStream()) {
			is = new ByteArrayInputStream(excelParam.getBuf());
			wb = WorkbookFactory.create(is);
		} else {
			fileType = excelParam.getFilePath().substring(excelParam.getFilePath().lastIndexOf(".") + 1, excelParam.getFilePath().length());
			is = new FileInputStream(excelParam.getFilePath());
			if (ExcelTypeEnum.EXCEL_THREE.getText().equals(fileType)) {
				wb = new HSSFWorkbook(is);
			} else if (ExcelTypeEnum.EXCEL_SEVEN.getText().equals(fileType)) {
				wb = new XSSFWorkbook(is);
			} else {
				throw new Exception("您输入的excel格式不正确");
			}
		}

		int startSheetNum = 0;
		int endSheetNum = 1;
		if (null != excelParam.getSheetIndex()) {
			startSheetNum = excelParam.getSheetIndex() - 1;
			endSheetNum = excelParam.getSheetIndex();
		}

		// 获取每个Sheet表
		for (int sheetNum = startSheetNum; sheetNum < endSheetNum; sheetNum++) {
			// 记录第x行为表头
			int rowNum_x = -1;
			// 存放每一个field字段对应所在的列的序号
			Map<String, Integer> cellmap = new HashMap<String, Integer>(20);
			// 存放所有的表头字段信息
			List<String> headlist = new ArrayList();

			Sheet hssfSheet = wb.getSheetAt(sheetNum);

			//判断表中是否含有合并单元格-为了忽略导出时候带的表头
			if (hssfSheet.getNumMergedRegions() > 0 ? true : false) {
				excelParam.setRowNumIndex(2);
			}
			// 设置默认最大行为50w行
			if (hssfSheet != null && hssfSheet.getLastRowNum() > 500000) {
				throw new Exception("Excel 数据超过50w行,请检查是否有空行,或分批导入");
			}

			// 循环行Row
			for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				// 如果传值指定从第几行开始读，就从指定行寻找，否则自动寻找
				if (excelParam.getRowNumIndex() != null && rowNum_x == -1) {
					rowNum = excelParam.getRowNumIndex() - 1;
					Row hssfRow = hssfSheet.getRow(rowNum);
					if (hssfRow == null) {
						throw new RuntimeException("指定的行为空，请检查");
					}
				}
				Row hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}
				boolean flag = false;
				for (int i = 0; i < hssfRow.getLastCellNum(); i++) {
					if (hssfRow.getCell(i) != null && !("").equals(hssfRow.getCell(i).toString().trim())) {
						flag = true;
					}
				}
				if (!flag) {
					continue;
				}
				if (rowNum_x == -1) {
					// 循环列Cell
					for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {

						Cell hssfCell = hssfRow.getCell(cellNum);
						if (hssfCell == null) {
							continue;
						}
						String tempCellValue = hssfSheet.getRow(rowNum).getCell(cellNum).getStringCellValue();
						tempCellValue = StringUtils.remove(tempCellValue, (char) 160);
						tempCellValue = tempCellValue.trim();

						headlist.add(tempCellValue);

						if (isJsonObj && StringUtils.isNotBlank(tempCellValue)) {
							rowNum_x = rowNum;
							cellmap.put(tempCellValue, cellNum);
							map.put(tempCellValue, tempCellValue);
							keySet.add(tempCellValue);
						} else {
							Iterator it = keySet.iterator();
							while (it.hasNext()) {
								Object key = it.next();
								if (StringUtils.isNotBlank(tempCellValue)
										&& StringUtils.equals(tempCellValue, key.toString())) {
									rowNum_x = rowNum;
									cellmap.put(map.get(key).toString(), cellNum);
								}
							}
						}

						if (rowNum_x == -1) {
							throw new Exception("没有找到对应的字段或者对应字段行上面含有不为空白的行字段");
						}
					}
					if (isJsonObj) {
						excelParam.setMap(map);
					}
					if (excelParam.getSameHeader()) {
						// 读取到列后，检查表头是否完全一致--start
						for (int i = 0; i < headlist.size(); i++) {
							boolean boo = false;
							Iterator itor = keySet.iterator();
							while (itor.hasNext()) {
								String tempname = itor.next().toString();
								if (tempname.equals(headlist.get(i))) {
									boo = true;
								}
							}
							if (boo == false) {
								throw new Exception("表头字段和定义的属性字段不匹配，请检查");
							}
						}
						Iterator itor = keySet.iterator();
						while (itor.hasNext()) {
							boolean boo = false;
							String tempname = itor.next().toString();
							for (int i = 0; i < headlist.size(); i++) {
								if (tempname.equals(headlist.get(i))) {
									boo = true;
								}
							}
							if (boo == false) {
								throw new Exception("表头字段和定义的属性字段不匹配，请检查");
							}
						}
						// 读取到列后，检查表头是否完全一致--end
					}
				} else {
					T obj = (T) excelParam.getClazz().newInstance();
					Iterator it = keySet.iterator();
					while (it.hasNext()) {
						Object key = it.next();
						String attr = "";
						if (isJsonObj) {
							attr = key.toString();
						} else {
							attr = map.get(key).toString();
						}

						Integer cellNum_x = cellmap.get(attr);
						if (cellNum_x == null || hssfRow.getCell(cellNum_x) == null) {
							continue;
						}
						Class<?> attrType = BeanUtils.findPropertyType(attr, new Class[]{obj.getClass()});
						Cell cell = hssfRow.getCell(cellNum_x);
						Object value = getValue(cell, obj, attr, attrType, rowNum, cellNum_x, key);

						if (isJsonObj) {
							setter(obj, value, key);
						} else {
							setter(obj, attr, value, attrType, rowNum, cellNum_x, key);
						}
					}
					list.add(obj);
				}
			}
		}
		is.close();
		return list;
	}


	public static void commonExportExcel(ExcelParam excelParam, Map<String, String> headInfoMap) throws Exception {
		Map<String, String> map = new HashMap<>(20);

		Class clazz = excelParam.getClazz();
		List list = excelParam.getList();
		List<String> keyList = null;

		if (Objects.equals(clazz, JSONObject.class) &&
				UtilValidate.isEmpty(excelParam.getMap()) &&
				list != null && list.size() > 0) {
			JSONObject firstRow = (JSONObject) list.get(0);
			Set<String> keySet = firstRow.keySet();
			for (String key : keySet) {
				map.put(key, key);
			}
			excelParam.setMap(map);
		}

		map = excelParam.getMap();

		if (UtilValidate.isEmpty(map)) {
			keyList = ExcelUtil.getList(clazz);
		} else {
			keyList = new ArrayList<String>(map.keySet());
		}

		Object obj = clazz.newInstance();
		// 创建HSSFWorkbook对象(excel的文档对象)
		XSSFWorkbook wb = new XSSFWorkbook();
		// 建立新的sheet对象（excel的表单）
		XSSFSheet sheet = wb.createSheet("sheet1");

		// 头部样式
		CellStyle headerStyle = wb.createCellStyle();
		headerStyle.setAlignment(HorizontalAlignment.CENTER);
		headerStyle.setWrapText(true);

		CellStyle titleStyle = wb.createCellStyle();
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		titleStyle.setWrapText(true);
		titleStyle.setBorderTop(BorderStyle.THICK);
		// 单元格样式
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);

		//存储最大列宽
		Map<Integer, Integer> maxWidth = new HashMap<Integer, Integer>(20);
		//如果有表头，字段头及表格创建行开始行
		int startRow = 0;
		//是否创建表头
		if (StringUtils.isNotEmpty(excelParam.getHeaderName())) {
			Row rowHeader = sheet.createRow(0);
			rowHeader.setHeight((short) (25 * 25));
			rowHeader.setRowStyle(headerStyle);
			Cell rowCell = rowHeader.createCell(0);
			rowCell.setCellStyle(headerStyle);
			rowCell.setCellValue(excelParam.getHeaderName());
			CellRangeAddress cra = new CellRangeAddress(0, 0, 0, (keyList.size() - 1));
			sheet.addMergedRegion(cra);
			startRow = 1;
		}

		// 添加表上部信息
		int infoQuantityInLine = 2;
		if (UtilValidate.isNotEmpty(headInfoMap) && headInfoMap.size() > 0) {
			Set<String> keySet = headInfoMap.keySet();
			int enterFlag = 0;
			Row rowHeader = sheet.createRow(startRow);
			rowHeader.setHeight((short) (25 * 25));
			rowHeader.setRowStyle(headerStyle);

			for (String key : keySet) {
				enterFlag++;
				if (enterFlag == (infoQuantityInLine + 1)) {
					enterFlag = 1;
					startRow++;
					rowHeader = sheet.createRow(startRow);
					rowHeader.setHeight((short) (25 * 25));
					rowHeader.setRowStyle(headerStyle);
				}

				int index = enterFlag * 4;
				Cell rowCell1 = rowHeader.createCell(index - 4);
				Cell rowCell2 = rowHeader.createCell(index - 3);
				Cell rowCell3 = rowHeader.createCell(index - 2);
				Cell rowCell4 = rowHeader.createCell(index - 1);

				rowCell1.setCellStyle(headerStyle);
				rowCell2.setCellStyle(headerStyle);
				rowCell3.setCellStyle(headerStyle);
				rowCell4.setCellStyle(headerStyle);

				rowCell1.setCellValue(key);
				rowCell3.setCellValue(headInfoMap.get(key));

				CellRangeAddress cra1 = new CellRangeAddress(startRow, startRow, index - 4, index - 3);
				CellRangeAddress cra2 = new CellRangeAddress(startRow, startRow, index - 2, index - 1);
				sheet.addMergedRegion(cra1);
				sheet.addMergedRegion(cra2);
			}
		}

		startRow++;
		// 在sheet里创建第x(取决于是否有表头，如果有表头，则在第2行创建，否则在第一行创建)行为表头，参数为行索引(excel的行)
		Row rowHeader = sheet.createRow(startRow);
		rowHeader.setHeight((short) (25 * 20));
		rowHeader.setRowStyle(headerStyle);

		//设置表head
		int index = 0;
		for (String key : keyList) {
			Cell rowCell = rowHeader.createCell(index);
			rowCell.setCellStyle(titleStyle);
			rowCell.setCellValue(map.get(key));
			maxWidth.put(index, rowCell.getStringCellValue().getBytes().length * 256 + 200);
			index++;
		}
		// 列宽自适应
		for (int i = 0; i < keyList.size(); i++) {
			sheet.setColumnWidth(i, maxWidth.get(i));
		}

		// 设置表格内容
		for (int i = 0; i < list.size(); i++) {
			JSONObject rowInfo = JSONObject.parseObject(JSON.toJSONString(list.get(i)));
			Row row = sheet.createRow(i + startRow + 1);
			row.setHeight((short) (25 * 18));
			for (int j = 0; j < keyList.size(); j++) {
				String field = keyList.get(j);
				Object value = rowInfo.get(field);
				if (null == value) {
					value = "";
				}
				Cell rowCell = row.createCell(j);
				rowCell.setCellStyle(cellStyle);
				rowCell.setCellValue(value.toString());
			}
		}
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		String newFileName = excelParam.getFileName();
		if (StringUtils.isEmpty(newFileName)) {
			newFileName = df.format(new Date());
		}
		// 输出Excel文件
		try {
			if (excelParam.getResponse() != null) {
				OutputStream outstream = excelParam.getResponse().getOutputStream();
				excelParam.getResponse().reset();
				excelParam.getResponse().setHeader("Content-disposition",
						"attachment; filename=" + new String(newFileName.getBytes(), StandardCharsets.ISO_8859_1) + ".xlsx");
				excelParam.getResponse().setContentType("application/x-download");
				wb.write(outstream);
				outstream.flush();
				outstream.close();
			} else {
				FileOutputStream out = new FileOutputStream(excelParam.getOutFilePath());
				wb.write(out);
				out.flush();
				out.close();
			}

		} catch (FileNotFoundException e) {
			throw new FileNotFoundException("导出失败！" + e);
		} catch (IOException e) {
			throw new IOException("导出失败！" + e);
		}
	}


	/**
	 * 把传进指定格式的字符串解析到Map中
	 *
	 * @param clazz
	 * @return java.util.Map<java.lang.String, java.lang.String>
	 */
	public static <T> Map<String, String> getMap(Class<T> clazz) throws NoSuchFieldException {

		Map<String, String> map = new HashMap<String, String>(20);
		Field field;
		Field[] fields = clazz.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			fields[i].setAccessible(true);
		}
		for (int i = 0; i < fields.length; i++) {
			field = clazz.getDeclaredField(fields[i].getName());
			Excel column = field.getAnnotation(Excel.class);
			if (column != null) {
				map.put(column.title(), field.getName());
			}
		}
		return map;
	}

	public static Object getValue(Cell cell, Object obj, String attr, Class attrType, int row, int col, Object key)
			throws Exception {
		Object val = null;

		if (cell.getCellType() == CellType.BOOLEAN) {
			val = cell.getBooleanCellValue();

		} else if (cell.getCellType() == CellType.NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				try {
					if (attrType == String.class) {
						val = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
					} else {
						val = dateConvertFormat(sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())));
					}
				} catch (ParseException e) {
					throw new Exception("第" + (row + 1) + " 行  " + (col + 1) + "列   属性：" + key + " 日期格式转换错误  ");
				}
			} else {
				if (attrType == String.class) {
					cell.setCellType(CellType.STRING);
					val = cell.getStringCellValue();
				} else if (attrType == BigDecimal.class) {
					val = new BigDecimal(cell.getNumericCellValue());
				} else if (attrType == long.class) {
					val = (long) cell.getNumericCellValue();
				} else if (attrType == Double.class) {
					val = cell.getNumericCellValue();
				} else if (attrType == Float.class) {
					val = (float) cell.getNumericCellValue();
				} else if (attrType == int.class || attrType == Integer.class) {
					val = (int) cell.getNumericCellValue();
				} else if (attrType == Short.class) {
					val = (short) cell.getNumericCellValue();
				} else {
					val = cell.getNumericCellValue();
				}
			}

		} else if (cell.getCellType() == CellType.STRING) {
			String cellVal = null;
			if (cell.getStringCellValue() != null && cell.getStringCellValue().trim().length() > 0) {
				cellVal = cell.getStringCellValue().trim();
				if (attrType.equals(double.class) || attrType.equals(Double.class)) {
					val = Double.parseDouble(cellVal);
				} else if (attrType == BigDecimal.class) {
					val = new BigDecimal(cellVal);
				} else if (attrType == long.class || attrType == Long.class) {
					val = Long.valueOf(cellVal);
				} else if (attrType == Float.class) {
					val = Float.valueOf(cellVal);
				} else if (attrType == int.class || attrType == Integer.class) {
					val = Integer.parseInt(cellVal);
				} else if (attrType == Short.class) {
					val = Short.valueOf(cellVal);
				} else if (attrType == Date.class) {
					val = dateConvertFormat(cellVal);
				} else {
					val = cell.getStringCellValue();
				}
			} else {
				val = null;
			}

		}
		//setter(obj, attr, val, attrType, row, col, key);
		return val;
	}

	public static void setter(Object obj, String att, Object value, Class<?> type, int row, int col, Object key)
			throws Exception {
		try {
			Method method = obj.getClass().getMethod("set" + StringUtil.toUpperCaseFirstOne(att), type);
			if (value != null) {
				method.invoke(obj, value);
			}
		} catch (Exception e) {
			throw new Exception("第" + (row + 1) + " 行  " + (col + 1) + "列   属性：" + key + " 赋值异常  " + e);
		}
	}

	public static void setter(Object obj, Object value, Object key) {
		JSONObject jsonObject = (JSONObject) obj;
		jsonObject.put(String.valueOf(key), value);
	}

	public static Date dateConvertFormat(String dateStr) throws ParseException {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = format.parse(dateStr);
		return date;
	}
}
